/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DataMobil;

import DBKoneksi.TestConection;
import DataPenyewa.DataPenyewa;
import java.sql.*;
import java.util.*;

/**
 *
 * @author bahrie
 */
public class Intermobil implements Intercar {

    @Override
    public DataMobil insert(DataMobil o) throws SQLException {
        PreparedStatement ps = TestConection.getConnection().prepareStatement("insert into DATAMOBIL values(?,?,?,?,?,?,?,?)");
        ps.setString(1, o.getId_mobil());
        ps.setString(2, o.getNama_mobil());
        ps.setString(3, o.getWarna());
        ps.setInt(4, o.getHarga_sewa());
        ps.setString(5, o.getStatus());
        ps.setString(6, o.getMerk_Mobil());
        ps.setString(7, o.getNo_polisi());
        ps.setString(8, o.getNo_STNK());
        ps.executeUpdate();
        return o;
    }

    @Override
    public void update(DataMobil o) throws SQLException {
        PreparedStatement ps = TestConection.getConnection().prepareStatement("update DATAMOBIL set NAMAMOBIL=?,"
                + "WARNAMOBIL=?,HARGASEWA=?,"
                + "STATUSMOBIL=?,MERKMOBIL=?,NOPOLISI=?,NOSTNK=? where IDMOBIL=?");
        ps.setString(1, o.getNo_kendaraan());
        ps.setString(3, o.getWarna());
        ps.setInt(4, o.getHarga_sewa());
        ps.setString(5, o.getStatus());
        ps.setString(6, o.getMerk_Mobil());
        ps.setString(7, o.getNo_polisi());
        ps.setString(8, o.getNo_STNK());
        ps.setString(9, o.getId_mobil());
        ps.executeUpdate();
    }

    @Override
    public void delete(String o) throws SQLException {
        PreparedStatement ps = TestConection.getConnection().prepareStatement("delete from DATAMOBIL where IDMOBIL=?");
        ps.setString(1, o);
        ps.executeUpdate();
    }

    @Override
    public List<DataMobil> getAll() throws SQLException {
        Statement st = TestConection.getConnection().createStatement();
        ResultSet rs = st.executeQuery("select * from DATAMOBIL");
        List<DataMobil> list = new ArrayList<DataMobil>();
        while (rs.next()) {
            DataMobil o = new DataMobil();
            o.setId_mobil(rs.getString("IDMOBIL"));
            o.setNama_mobil(rs.getString("NAMAMOBIL"));
            o.setWarna(rs.getString("WARNAMOBIL"));
            o.setHarga_sewa(rs.getInt("HARGASEWA"));
            o.setStatus(rs.getString("STATUSMOBIL"));
            o.setMerk_Mobil(rs.getString("MERKMOBIL"));
            o.setNo_polisi(rs.getString("NOPOLISI"));
            o.setNo_STNK(rs.getString("NOSTNK"));
            list.add(o);
        }
        return list;
    }

    @Override
    public List<DataMobil> getByNamaMobil(String nama) throws SQLException {
        PreparedStatement ps = TestConection.getConnection().prepareStatement("select * from DATAMOBIL where NAMAMOBIL like ?");
        ps.setString(1, "%" + nama + "%");
        ResultSet rs = ps.executeQuery();
        List<DataMobil> list = new ArrayList<DataMobil>();
        while (rs.next()) {
            DataMobil o = new DataMobil();
            o.setId_mobil(rs.getString("IDMOBIL"));
            o.setNama_mobil(rs.getString("NAMAMOBIL"));
            o.setWarna(rs.getString("WARNAMOBIL"));
            o.setHarga_sewa(rs.getInt("HARGASEWA"));
            o.setStatus(rs.getString("STATUSMOBIL"));
            o.setMerk_Mobil(rs.getString("MERKMOBIL"));
            o.setNo_polisi(rs.getString("NOPOLISI"));
            o.setNo_STNK(rs.getString("NOSTNK"));
            list.add(o);
        }
        return list;
    }

    @Override
    public List<DataMobil> getBymrek(String mrk) throws SQLException {
        PreparedStatement ps = TestConection.getConnection().prepareStatement("select * from DATAMOBIL where MERKMOBIL like ?");
        ps.setString(1, "%" + mrk + "%");
        ResultSet rs = ps.executeQuery();
        List<DataMobil> list = new ArrayList<DataMobil>();
        while (rs.next()) {
            DataMobil o = new DataMobil();
            o.setId_mobil(rs.getString("IDMOBIL"));
            o.setNama_mobil(rs.getString("NAMAMOBIL"));
            o.setWarna(rs.getString("WARNAMOBIL"));
            o.setHarga_sewa(rs.getInt("HARGASEWA"));
            o.setStatus(rs.getString("STATUSMOBIL"));
            o.setMerk_Mobil(rs.getString("MERKMOBIL"));
            o.setNo_polisi(rs.getString("NOPOLISI"));
            o.setNo_STNK(rs.getString("NOSTNK"));
            list.add(o);
        }
        return list;
    }

    @Override
    public List<DataMobil> getBywarna(String wrna) throws SQLException {
        PreparedStatement ps = TestConection.getConnection().prepareStatement("select * from DATAMOBIL where WARNAMOBIL like ?");
        ps.setString(1, "%" + wrna + "%");
        ResultSet rs = ps.executeQuery();
        List<DataMobil> list = new ArrayList<DataMobil>();
        while (rs.next()) {
            DataMobil o = new DataMobil();
            o.setId_mobil(rs.getString("IDMOBIL"));
            o.setNama_mobil(rs.getString("NAMAMOBIL"));
            o.setWarna(rs.getString("WARNAMOBIL"));
            o.setHarga_sewa(rs.getInt("HARGASEWA"));
            o.setStatus(rs.getString("STATUSMOBIL"));
            o.setMerk_Mobil(rs.getString("MERKMOBIL"));
            o.setNo_polisi(rs.getString("NOPOLISI"));
            o.setNo_STNK(rs.getString("NOSTNK"));
            list.add(o);
        }
        return list;
    }

    @Override
    public DataMobil getByOne(String o) throws SQLException {
        PreparedStatement ps = TestConection.getConnection().prepareStatement("select *from DATAMOBIL where IDMOBIL=?");
        ps.setString(1, o);
        ResultSet rs = ps.executeQuery();
        DataMobil mbl = new DataMobil();
        if (rs.next()) {

            mbl.setId_mobil(rs.getString("IDMOBIL"));
            mbl.setNama_mobil(rs.getString("NAMAMOBIL"));
            mbl.setWarna(rs.getString("WARNAMOBIL"));
            mbl.setHarga_sewa(rs.getInt("HARGASEWA"));
            mbl.setStatus(rs.getString("STATUSMOBIL"));
            mbl.setMerk_Mobil(rs.getString("MERKMOBIL"));
            mbl.setNo_polisi(rs.getString("NOPOLISI"));
            mbl.setNo_STNK(rs.getString("NOSTNK"));

        }
        return mbl;
    }

    public DataPenyewa getByIdPenyewa(DataPenyewa penyewa) throws SQLException {
        CallableStatement call = null;
        String sql = "call SPCARIpenyewa(?,?,?,?)";
        call = TestConection.getConnection().prepareCall(sql);
        call.setString(1, penyewa.getID_penyewa());
        call.registerOutParameter(2, java.sql.Types.VARCHAR);
        call.registerOutParameter(3, java.sql.Types.VARCHAR);
        call.registerOutParameter(4, java.sql.Types.VARCHAR);
        call.execute();

        String nama = call.getString(2);
        String alamat = call.getString(3);
        String telpon = call.getString(4);

        call.execute();

        penyewa.setNama_penyewa(nama);
        penyewa.setAlamat(alamat);
        penyewa.setNo_telepon(telpon);

        return penyewa;

    }

    public DataMobil getByIdMobil(DataMobil mobil) throws SQLException {
        CallableStatement call = null;
        String sql = "call SPCARIMOBIL(?,?,?,?,?)";
        call = TestConection.getConnection().prepareCall(sql);
        call.setString(1, mobil.getId_mobil());
        call.registerOutParameter(2, java.sql.Types.VARCHAR);
        call.registerOutParameter(3, java.sql.Types.VARCHAR);
        call.registerOutParameter(4, java.sql.Types.VARCHAR);
        call.registerOutParameter(5, java.sql.Types.INTEGER);
        call.execute();

        String nama = call.getString(2);
        String warna = call.getString(3);
        String merk = call.getString(4);
        int hargasewa = call.getInt(5);

        call.execute();

        mobil.setNama_mobil(nama);
        mobil.setWarna(warna);
        mobil.setMerk_Mobil(merk);
        mobil.setHarga_sewa(hargasewa);
        return mobil;

    }
      @Override
    public List<DataSewa> getJadwal(String tanggalSewa) throws SQLException {
        PreparedStatement ps = TestConection.getConnection().prepareStatement("select idmobil, namamobil, idpenyewa, namapenyewa, tanggalsewa, tanggalkembali from datapenyewaan where tanggalsewa='" + tanggalSewa + "'");
        List<DataSewa> list = new ArrayList<DataSewa>();
        ResultSet rs = ps.executeQuery();

        while (rs.next()) {
            DataSewa s = new DataSewa();

            s.setId_mobil(rs.getString("IDMOBIL"));
            s.setNama_mobil(rs.getString("NAMAMOBIL"));
            s.setId_penyewa(rs.getString("IDPENYEWA"));
            s.setNama_penyewa(rs.getString("NAMAPENYEWA"));
            s.setTgl_sewa(rs.getString("TANGGALSEWA"));
            s.setTgl_kembali(rs.getString("TANGGALKEMBALI"));


            list.add(s);
        }
        return list;


    }
  public DataSewa getBynoTransaksi(DataSewa sewa) throws SQLException {
        CallableStatement call = null;
        String sql = "call SPCARIPENGEMBALIAN(?,?,?,?,?,?,?,?,?,?)";
        call = TestConection.getConnection().prepareCall(sql);
        call.setString(1, sewa.getNotransaksi());
        call.registerOutParameter(2, java.sql.Types.VARCHAR);
        call.registerOutParameter(3, java.sql.Types.VARCHAR);
        call.registerOutParameter(4, java.sql.Types.VARCHAR);
        call.registerOutParameter(5, java.sql.Types.VARCHAR);
        call.registerOutParameter(6, java.sql.Types.VARCHAR);
        call.registerOutParameter(7, java.sql.Types.VARCHAR);
        call.registerOutParameter(8, java.sql.Types.VARCHAR);
        call.registerOutParameter(9, java.sql.Types.VARCHAR);
        call.registerOutParameter(10, java.sql.Types.VARCHAR);
        call.execute();

        String nama_operator = call.getString(2);
        String id_penyewa = call.getString(3);
        String nama_penyewa = call.getString(4);
        String id_mobil = call.getString(5);
        String nama_mobil = call.getString(6);
        String merk_mobil = call.getString(7);
        String warna_mobil = call.getString(8);
        String tanggal_sewa = call.getString(9);
        String tanggal_kembali = call.getString(10);
        call.execute();


        sewa.setNamaOperator(nama_operator);
        sewa.setId_penyewa(id_penyewa);
        sewa.setNama_penyewa(nama_penyewa);
        sewa.setId_mobil(id_mobil);
        sewa.setNama_mobil(nama_mobil);
        sewa.setMerkMobil(merk_mobil);
        sewa.setWarnaMobil(warna_mobil);
        sewa.setTgl_sewa(tanggal_sewa);
        sewa.setTgl_kembali(tanggal_kembali);
        return sewa;
    }
public void getBynotran(DataSewa s) throws SQLException {
        PreparedStatement ps = TestConection.getConnection().prepareStatement("select HARGASEWA, TOTAL from DATAPENYEWAAN where NOTRANSAKSISEWA=?");
        ps.setString(1, s.getNotransaksi());
        ResultSet rs = ps.executeQuery();
        if (rs.next()) {
            s.setHargasewa(rs.getInt("HARGASEWA"));
            s.setTotal(rs.getDouble("TOTAL"));
        }
    }

public void updatestatus(DataMobil o) throws SQLException {
        PreparedStatement ps = TestConection.getConnection().prepareStatement("update DATAMOBIL set STATUSMOBIL=? where IDMOBIL=?");
        ps.setString(1, o.getStatus());
        ps.setString(2, o.getId_mobil());
        ps.executeUpdate();
    }

public void deletesewa(String sewa) throws SQLException {
        PreparedStatement ps = TestConection.getConnection().prepareStatement("delete from DATAPENYEWAAN where NOTRANSAKSISEWA=?");
        ps.setString(1, sewa);
        ps.executeUpdate();
    }

public void insertakhr(DataSewa sewa) throws SQLException {
        PreparedStatement ps = TestConection.getConnection().prepareStatement("insert into DATASEWA values(?,?,?,?,?,?,?,?,?,?)");
        ps.setString(1, sewa.getNotransaksi());
        ps.setString(2, sewa.getTgl_sewa());
        ps.setString(3, sewa.getTgl_kembali());
        ps.setString(4, sewa.getId_penyewa());
        ps.setString(5, sewa.getNama_penyewa());
        ps.setString(6, sewa.getId_mobil());
        ps.setString(7, sewa.getNama_mobil());
        ps.setString(8, sewa.getMerkMobil());
        ps.setString(9, sewa.getWarnaMobil());
        ps.setDouble(10, sewa.getTotal());
        ps.executeUpdate();

    }
}
